''' 人脸识别器 '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB
from utils.db_config import mysqlInfo


logger = logging.getLogger('app')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'],cursorclass=pymysql.cursors.DictCursor)
    return pool.connection()

'''
人脸识别器下发 start
'''
# 更新设备状态
def update_status(msg: dict[str,str]):
    conn = connect_db()
    cur = conn.cursor()
    equipId = msg['sn']
    # update_time = datetime.datetime.fromtimestamp(msg['timestamp'])
    try:
        # 判断一下雇员id和设备id是否存在相应的记录
        sql = ("UPDATE equipment SET is_online = %s, online_date = %s WHERE id = %s")
        # print(sql)
        cur.execute(sql, ['在线', datetime.datetime.now(), equipId])
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(result)

def auto_update_status():
    conn = connect_db()
    cur = conn.cursor()
    try:
        # 判断一下雇员id和设备id是否存在相应的记录
        sql = ("UPDATE equipment SET is_online = %s WHERE TIMESTAMPDIFF(SECOND, online_date, NOW()) > 30")
        # print(sql)
        cur.execute(sql, ['离线'])
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(result)

#下发结果录入系统
def release_equipment(msg: dict[str, dict]):
    conn = connect_db()
    cur = conn.cursor()
    equipId = msg.get('from')
    data = msg.get('data')
    employeeId = int(data['user_id'])
    status = data['msg']
    print(msg)
    try:
        #判断一下雇员id和设备id是否存在相应的记录
        sql = ("SELECT id FROM release_equipment WHERE emp_id = %s AND equip_id = %s")
        print(sql,employeeId,equipId)
        cur.execute(sql,[employeeId,equipId])
        # 判断一下是否已经有相应的下发记录
        fetchone = cur.fetchone()
        if fetchone:
            releaseId = fetchone['id']
        else:
            releaseId = None
        if releaseId:
            sql = ("UPDATE release_equipment SET emp_id = %s, equip_id = %s, status = %s, rls_time = %s WHERE id = %s")
            print(sql)
            cur.execute(sql,[employeeId,equipId,status,datetime.datetime.now(),releaseId])
        else:
            sql = ("INSERT INTO release_equipment (emp_id,equip_id,status,rls_time) VALUES (%s, %s, %s, %s)")
            print(sql)
            cur.execute(sql, [employeeId,equipId,status,datetime.datetime.now()])
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(result)

# 删除信息录入系统
def delUser(msg:dict[str,dict]):
    conn = connect_db()
    cur = conn.cursor()
    equipId = msg.get('from')
    data = msg.get('data')
    employeeId = int(data['user_id'])
    status = data['msg']
    if status != "删除成功":
        return '删除失败'
    try:
        # 判断一下雇员id和设备id是否存在相应的记录
        sql = ("DELETE FROM release_equipment WHERE emp_id = %s AND equip_id = %s")
        print(sql, employeeId, equipId)
        cur.execute(sql, [employeeId, equipId])
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(result)


#下发人员识别结果录入系统
def faceRecogn_data(msg: dict[str, list]):
    conn = connect_db()
    cur = conn.cursor()
    equipId = msg.get('sn')
    logs = msg.get('logs')
    result = {}
    try:
        print("下发记录")
        # 确定记录是签到还是签退
        sql = ("SELECT type FROM equipment WHERE id = %s")
        print(sql)
        cur.execute(sql,[equipId])
        checkType = cur.fetchone()['type']
        if checkType == 0:
            checkType = 'check_in_time'
        elif checkType == 1:
            checkType = 'check_out_time'
        print(checkType)
        # 录入数据
        for log in logs:
            employeeId = log['user_id']
            checkTime = log['recog_time']
            sql = (f"INSERT INTO employee_check_record (device_id, employee_id, {checkType}) VALUES (%s, %s, %s)")
            print(sql)
            cur.execute(sql, [equipId,employeeId,checkTime])
        conn.commit()
        result = {
            "Result":0,
            "Msg":"更新成功"
        }
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
'''
人脸识别器下发 end
'''
